package com.yue.excel;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Random;

/**
 * @Author: yyk
 * @Description: 模拟生成excel数据
 * @Date: Created in 17:02 2024/8/4
 * @Since version-1.0
 */
public class CreateExcelData {

    @Test
    public void createData(){
        System.out.println("生成excel数据……");
        String excelFilePath = "D:\\Program Files\\JetBrains\\excel_util\\2024数据生成.xlsx";

        try (FileInputStream inputStream = new FileInputStream(excelFilePath);
             Workbook workbook = new XSSFWorkbook(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0); // 这里选择第一个表格

            int rowNum = sheet.getLastRowNum(); // 获取最后一行的索引

            // 随机数生成
            Random random = new Random();

            int randomNumber = random.nextInt(4);  // 生成的范围是0到3

            // 模拟生成数据数据，从索引为1的行开始 索引0是标题
            for (int i = 1; i < 100; i++) {
                Row row = sheet.createRow(++rowNum);

                // 模拟生成数据，产品代码
                Cell productCodCell = row.createCell(0);
                productCodCell.setCellValue("ABC" + 10 + i);

                // 时效
                Cell ageingCell = row.createCell(1);
                ageingCell.setCellValue("T+" + randomNumber);//随机生成

                // 随机生成是否
                Cell yesOrNoCell = row.createCell(2);
                String yesOrNo = random.nextBoolean() ? "是" : "否";
                yesOrNoCell.setCellValue(yesOrNo);

                //日期
                Cell dateCell = row.createCell(3);
                String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
                dateCell.setCellValue(time);

                // 自动调整列宽
                sheet.autoSizeColumn(i);
            }

            // 更新并保存Excel文件
            try (FileOutputStream outputStream = new FileOutputStream(excelFilePath)) {
                workbook.write(outputStream);
            }

            System.out.println("数据成功写入到Excel文件中。");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
